﻿using OfficeOpenXml;
using System.Collections.Generic;
using System.IO;
using System.Windows;
using System.Windows.Controls;

using System;

using System.Collections.ObjectModel;
using System.Data;
using QBData.Entity;

namespace QBAnalysis
{
	/// <summary>
	/// ImportData.xaml 的交互逻辑
	/// </summary>
	public partial class ImportData : UserControl
	{
		public ImportData()
		{
			InitializeComponent();
			dataGrid1.ItemsSource = originalBills;
		}

		ObservableCollection<OriginalBill> originalBills = new ObservableCollection<OriginalBill>();

		/// <summary>
		/// 打开文件
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void button_Click(object sender, RoutedEventArgs e)
		{
			string phone;

			#region 打开导入文件窗体
			System.Windows.Forms.OpenFileDialog openFileDialog = new System.Windows.Forms.OpenFileDialog
			{
				AutoUpgradeEnabled = true,
				FilterIndex = 1,
				RestoreDirectory = true,
				FileName = string.Empty,
				Filter = "xlsx文件|*.xlsx",
				Title = "打开文件"
			};
			if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK && openFileDialog.FileName != string.Empty)
			{
				Text_FileName.Text = openFileDialog.FileName;
				phone = openFileDialog.SafeFileName.Substring(0, 11);
			}
			else
			{
				return;
			}
			#endregion

			try
			{
				using (ExcelHelper excelHelper = new ExcelHelper(Text_FileName.Text))
				{
					DataTable dt = new DataTable();

					dt = excelHelper.ExcelToDataTable("sheet1", true);//读取数据  
					foreach (DataRow dr in dt.Rows)//DataTable转ObservableCollection  
					{
						originalBills.Add(GetOriginal(dr));
					}
				}
			}
			catch (Exception E)
			{
				MessageBox.Show(E.Message);
			}

			try {
				SaveTb_bill(originalBills,phone);
			} catch (Exception ex) { MessageBox.Show(ex.Message); }
		}

		public OriginalBill GetOriginal(DataRow dr)
		{
			OriginalBill original = new OriginalBill()
			{
				Id = dr[0].ToString(),
				StartTime = dr[1].ToString(),
				LocationOfCorrespondence = dr[2].ToString(),
				CommunicationMode = dr[3].ToString(),
				OtherPartyNumber = dr[4].ToString(),
				CommunicationDuration = dr[5].ToString(),
				CommunicationType = dr[6].ToString(),
				NetworkType = dr[7].ToString(),
				SetMealPreferences = dr[8].ToString(),
				BasicCost = dr[9].ToString(),
				LongDistanceCharges = dr[10].ToString(),
				OtherExpenses = dr[11].ToString(),
				ReceivedCommunicationFee = dr[12].ToString(),
				AccountPeriodStartDate = dr[13].ToString(),
				AccountPeriodEndDate = dr[14].ToString(),
				BoundaryWalkOrNot = dr[15].ToString(),
				CellNumberOfBaseStation = dr[16].ToString(),
				IMEI = dr[17].ToString(),
				IMSI = dr[18].ToString(),
			};
			return original;
		}

		/// <summary>
		///存储话单数据
		/// </summary>
		/// <param name="originalBills"></param>
		public void SaveTb_bill(ObservableCollection<OriginalBill> originalBills, string phone)
		{
			foreach (var item in originalBills)
			{
				Bill bill = new Bill()
				{
					//BillNumber
					StartingTime = item.StartTime,
					TalkTime = item.CommunicationDuration,
					//attribution_of_opposite_party
					//regional_span
					NetworkType = item.NetworkType,
					BusinessType = item.CommunicationMode, 
					DiscountPackage=item.SetMealPreferences,
					ActualCost=item.ReceivedCommunicationFee,
					//mobile_device_id
					//mobile_user_id
					//remarks
				};

				if (item.CommunicationMode == "被叫")
				{
					bill.CallingNumber = item.OtherPartyNumber;
					bill.TheOtherPartyNumber = phone;
					bill.CalledBaseStationNumber = item.CellNumberOfBaseStation;
				}
				else {
					//主叫，
					bill.CallingArea = item.LocationOfCorrespondence;
					bill.CallingBaseStationNumber = item.CellNumberOfBaseStation; 
					bill.CallingNumber = phone;
					bill.TheOtherPartyNumber = item.OtherPartyNumber;
				}

			}
			//TODO:存储话单表
		}

		/// <summary>
		/// 获取工作表的sheet名字
		/// </summary>
		/// <returns></returns>
		public List<string> GetSheetName(string path)
		{
			//Excel文件所在的地址
			FileInfo file = new FileInfo(path);
			//存放所有sheet名的list
			List<string> sheetName = new List<string>();
			using (ExcelPackage excelPackage = new ExcelPackage(file))
			{
				//得到ExcelWorkbook,所有sheet就在这个里面         
				ExcelWorkbook excelWorkbook = excelPackage.Workbook;

				foreach (var worksheet in excelWorkbook.Worksheets)
				{
					//将所有sheet名字添加进去
					sheetName.Add(worksheet.Name);
				}
			}
			return sheetName;
		}

	}
}
